package com.zyz.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.zyz.entity.User;
import com.zyz.utils.JdbcUtils;



public class UserDao {

	Connection conn = null;

	PreparedStatement ps=null;

	ResultSet rs = null;   
/**   
 * ��¼       
 * @param username    
 * @param passwd
 * @return
 */
	public User login(String username, String passwd) {


		User user = new User();

		try {
			conn = JdbcUtils.getConnection();

			String sql = "SELECT * FROM user WHERE username='" + username
					+ "' and passwd='" + passwd + "'";
			
			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery(sql);

			while (rs.next()) {
				
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPasswd(rs.getString("passwd"));
				return user;
			}

			return user;

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}
		

	}
	
	/**
	 * ��������ѯ�û� 
	 * @return
	 */

	public List<User> findAll(String userName,String sex,String place,String hobby) {

		List<User> listuser = new ArrayList<User>();

		try {
			conn = JdbcUtils.getConnection();

			String sql = "select*from user where 1= 1 ";
			
			if(null != userName && !"".equals(userName) ){
				sql+= " and username  = '%'"+userName+"'%'";
			}
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {

				User user1 = new User();
				user1.setId(rs.getInt("id"));
				user1.setUsername(rs.getString("username"));
				user1.setPasswd(rs.getString("passwd"));
				user1.setSex(rs.getString("sex"));
				user1.setAge(rs.getInt("age"));
				user1.setPlace(rs.getString("place"));
				user1.setHobby(rs.getString("hobby"));

				listuser.add(user1);

			}

			return listuser;

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}

	}
	
	/**
	 * ����û�
	 * @param user
	 */
	public void addUser(User user)  {

	

		try {
			conn = JdbcUtils.getConnection();

			String sql = "insert into user(username,passwd,sex,age,place,hobby) values(?,?,?,?,?,?)";
			
			
			ps = conn.prepareStatement(sql);

			ps.setString(1, user.getUsername());

			ps.setString(2, user.getPasswd());
			
			ps.setString(3, user.getSex());
			
			ps.setInt(4, user.getAge());
			
			ps.setString(5, user.getPlace());
			
			ps.setString(6, user.getHobby());

			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}

	}
	
	/**
	 * ɾ���û�
	 * @param id
	 */
	
	public void delet(int id)  {

	

		try {
			conn = JdbcUtils.getConnection();

			String sql = "delete from user where id=" + id;
			
			
			ps = conn.prepareStatement(sql);

			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}

	}
	
	/**
	 * �����û�
	 * @param id
	 * @return
	 */
	public User findById(int id)  {

		User user = new User();

		try {
			conn = JdbcUtils.getConnection();

			String sql = "select * from user where id=" + id;
			
			
			ps = conn.prepareStatement(sql);

			rs=ps.executeQuery(sql);
			
			
			
			while (rs.next()) {
				
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPasswd(rs.getString("passwd"));
				user.setSex(rs.getString("sex"));
				user.setAge(rs.getInt("age"));
				user.setPlace(rs.getString("place"));
				user.setHobby(rs.getString("hobby"));

			}
			return user;

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}
		

	}
	
	/**
	 * �޸��û�
	 * @param user
	 */
	
	public void update(User user)  {

		

		try {
			conn = JdbcUtils.getConnection();

			String sql = "update user set username='" + user.getUsername()
					+ "'," +
						" sex='" + user.getSex()+ "'" +
						", age='" + user.getAge() + "'" +
						", place='" + user.getPlace() + "'" +
						", hobby='" + user.getHobby() + "'where id='"
					+ user.getId() + "'";

			
			ps = conn.prepareStatement(sql);

			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}

	}

	/**
	 * 
	 * @param userId
	 * @return
	 */
	
	public List<User> findAllFriends(int userId) {

		List<User> listuser = new ArrayList<User>();

		try {
			conn = JdbcUtils.getConnection();

			String sql = "SELECT * FROM `user` WHERE `user`.id = " +
					" any (SELECT `friend`.friend_id FROM friend where user_id ="+userId+" )" +
					"OR `user`.id =" +
					" any (SELECT `friend`.user_id FROM  friend WHERE friend_id = "+userId+");";
			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {

				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPasswd(rs.getString("passwd"));
				user.setSex(rs.getString("sex"));
				user.setAge(rs.getInt("age"));
				user.setPlace(rs.getString("place"));
				user.setHobby(rs.getString("hobby"));

				listuser.add(user);

			}

			return listuser;

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {

			JdbcUtils.closeAll(conn, ps, rs);

		}

	}

}
